﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PgBirdie.Core.Config;
using PgBirdie.Core.Extensions;
using PgBirdie.Core.Helpers;
using PgBirdie.Core.Model;
using PgBirdie.Core.Model.Database.AccessControl;
using PgBirdie.Core.Model.Database.Data;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Model.Database.Structure.Constraint;
using PgBirdie.Core.Model.Database.Structure.Index;
using PgBirdie.Core.Service.Compare;
using PgBirdie.Core.Service.DbObject;
using PgBirdie.Core.Service.Delta;
using PgBirdie.Core.Service.Dump;
using PgBirdie.Core.Service.Infrastructure;
using PgBirdie.Core.Service.SourceControl;

namespace PgBirdie.Console.Command
{
    public class DeltaCommand : ICommand
    {
        private readonly ICompareService _compare;
        private readonly ICompareExtractService _compareExtract;
        private readonly IDbFromPgDumpService _dbExtract;
        private readonly IDbObjectFromDbService _dbObjectFromDb;
        private readonly IDbObjectToSqlService _dbObjectToSql;
        private readonly IDeltaStaticDataDbObjectToSqlService _deltaStaticDataDbObjectToSql;
        private readonly IDeltaStructureDbObjectToSqlService _deltaDbObjectToSql;
        private readonly IFileSystemService _fileSystem;        
        private readonly IPgDumpService _pgDump;
        private readonly IDbFromSourceControlService _sourceControlExtract;
        
        private readonly StringBuilder _sql;

        private PgDatabase _sourceDb;
        private PgDatabase _targetDb;
        private IDictionary<PersistenceType, IDictionary<string, PgSchema>> _schemas;
        private IDictionary<PersistenceType, IDictionary<string, PgSequence>> _sequences;
        private IDictionary<PersistenceType, IDictionary<string, PgTable>> _tables;
        private IDictionary<PersistenceType, IDictionary<string, PgIndex>> _indexes;
        private IDictionary<PersistenceType, IDictionary<string, PgConstraintBase>> _constraints;
        private IDictionary<PersistenceType, IDictionary<string, PgDataRow>> _dataRows;

        public DeltaCommand(ICompareService compare, ICompareExtractService compareExtract, IDbFromPgDumpService dbExtract,
            IDbObjectFromDbService dbObjectFromDb, IDbObjectToSqlService dbObjectToSql, IDeltaStaticDataDbObjectToSqlService deltaStaticDataDbObjectToSql,
            IDeltaStructureDbObjectToSqlService deltaDbObjectToSql, IFileSystemService fileSystem, IPgDumpService pgDump,
            IDbFromSourceControlService sourceControlExtract)
        {
            _compare = compare;
            _compareExtract = compareExtract;
            _dbExtract = dbExtract;
            _dbObjectFromDb = dbObjectFromDb;
            _dbObjectToSql = dbObjectToSql;
            _deltaStaticDataDbObjectToSql = deltaStaticDataDbObjectToSql;
            _deltaDbObjectToSql = deltaDbObjectToSql;
            _fileSystem = fileSystem;
            _pgDump = pgDump;
            _sourceControlExtract = sourceControlExtract;
            _sql = new StringBuilder();
        }

        /// <summary>
        /// Creates a delta/change script on disk that you can run against a production database. Caution is adviced.
        /// Review the script. No warranties, liabilities, etc. Use at your own risk.
        /// </summary>
        /// <remarks>
        /// When creating the delta file no byte order mark is used to avoid deployment issues when, for example,
        /// automatically executing the delta file with psql.exe.
        /// TODO: Remove this "hack" if/when psql.exe starts supporting UTF-8 files with BOM or
        /// at least provides a command line option to define what encoding the executed file will use.
        /// Some discussion about the subject: http://www.pubbs.net/pgsql/200910/37783/
        /// </remarks>
        public ExitCodeType Run()
        {
            var pathToDeltaFile = String.Format("{0}\\{1}_delta.sql", Settings.PathTo.Delta, Settings.Source.Database);
            _fileSystem.DeleteFile(pathToDeltaFile);

            if (!PopulateSourceDb())
                return ExitCodeType.Failure;

            if (!PopulateTargetDb())
                return ExitCodeType.Failure;

            var structuresHaveDifferences = _compare.TablesHaveDifferences(_sourceDb, _targetDb);
            var staticDataHaveDifferences = _compare.StaticDataHaveDifferences(_sourceDb, _targetDb);
            var accessControlHasDifferences = _compare.AccessControlHasDifferences(_sourceDb, _targetDb);

            if (!structuresHaveDifferences && !staticDataHaveDifferences && !accessControlHasDifferences)
            {
                const string msg = "Source and target databases did not have differences. They are in sync.";
                _fileSystem.CreateFile(pathToDeltaFile, String.Format("-- {0}", msg), false);
                AppConsole.WriteLine(msg);
                return ExitCodeType.Success;
            }

            PopulateObjects(structuresHaveDifferences, staticDataHaveDifferences);

            //if (staticDataHaveDifferences)
            //    DeleteExistingData();

            if (structuresHaveDifferences)
                ProcessStructureChanges();

            if (staticDataHaveDifferences)
                ProcessExistingData();

            if (accessControlHasDifferences)
                ProcessAccessControlChanges();

            if (_fileSystem.CreateFile(pathToDeltaFile, EncloseInTransaction(_sql).ToString(), false))
            {
                AppConsole.WriteLine("Successfully created delta file for database {0} to {1}.", _targetDb.Name, pathToDeltaFile);
                return ExitCodeType.Success;
            }

            AppConsole.WriteLine("Could not create delta file for {0}.", _targetDb.Name);
            return ExitCodeType.Failure;
        }

        private bool PopulateSourceDb()
        {
            _sourceDb = _sourceControlExtract.GetDbStructure();

            if (_sourceDb == null)
            {
                AppConsole.WriteLine("Could not extract source database from files.");
                return false;
            }

            _sourceControlExtract.PopulateStaticData(ref _sourceDb);

            return true;
        }

        private bool PopulateTargetDb()
        {
            var rawStructureDump = _pgDump.GetRawStructureFromPgDump(DatabaseType.Target);

            if (String.IsNullOrEmpty(rawStructureDump))
            {
                AppConsole.WriteLine("Could not extract target db dump from remote location.");
                return false;
            }

            _targetDb = _dbExtract.GetDbStructureFromPgDump(rawStructureDump);

            if (_targetDb == null || _targetDb.Schemas.IsNullOrEmpty())
            {
                AppConsole.WriteLine("Could not extract target db structure from target db dump.");
                return false;
            }

            var rawStaticDataDump = _pgDump.GetRawStaticDataFromPgDump(DatabaseType.Target);
            if (!String.IsNullOrWhiteSpace(rawStaticDataDump))
                _dbExtract.PopulateStaticDataFromPgDump(ref _targetDb, rawStaticDataDump);

            return true;
        }

        private void PopulateObjects(bool structuresHaveDifferences, bool staticDataHaveDifferences)
        {
            if (structuresHaveDifferences)
            {
                _schemas = _compareExtract.GetSchemas(_sourceDb, _targetDb);
                _sequences = _compareExtract.GetSequences(_sourceDb, _targetDb);
                _tables = _compareExtract.GetTables(_sourceDb, _targetDb);
                _indexes = _compareExtract.GetIndexes(_sourceDb, _targetDb);
                _constraints = _compareExtract.GetConstraints(_sourceDb, _targetDb);
            }
            
            if (staticDataHaveDifferences)
                _dataRows = _compareExtract.GetDataRows(_sourceDb, _targetDb);
        }

        private void DeleteExistingData()
        {
            _sql.Append(_deltaStaticDataDbObjectToSql.DeleteDataRows(_dataRows[PersistenceType.Delete]));
        }

        private void ProcessStructureChanges()
        {
            _sql.Append(_deltaDbObjectToSql.DropConstraints(_constraints[PersistenceType.Delete]));            

            _sql.Append(_deltaDbObjectToSql.DropIndexes(_indexes[PersistenceType.Delete]));

            _sql.Append(_deltaDbObjectToSql.DropTables(_tables[PersistenceType.Delete]));

            _sql.Append(_deltaDbObjectToSql.DropSchemas(_schemas[PersistenceType.Delete]));

            _sql.Append(_deltaDbObjectToSql.CreateSchemas(_schemas[PersistenceType.Add]));

            _sql.Append(_deltaDbObjectToSql.CreateSequences(_sequences[PersistenceType.Add]));

            _sql.Append(_deltaDbObjectToSql.CreateTables(_tables[PersistenceType.Add]));

            _sql.Append(_deltaDbObjectToSql.AlterTables(_tables[PersistenceType.Keep], _targetDb));            

            _sql.Append(_deltaDbObjectToSql.CreateIndexes(_indexes[PersistenceType.Add]));

            _sql.Append(_deltaDbObjectToSql.AddConstraints(_constraints[PersistenceType.Add]));
        }

        private void ProcessExistingData()
        {
            _sql.Append(_deltaStaticDataDbObjectToSql.DeleteDataRows(_dataRows[PersistenceType.Delete]));
            _sql.Append(_deltaStaticDataDbObjectToSql.UpdateDataRows(_dataRows[PersistenceType.Change], _targetDb));
            _sql.Append(_deltaStaticDataDbObjectToSql.InsertDataRows(_dataRows[PersistenceType.Add]));
        }

        private void ProcessAccessControlChanges()
        {
            var sourceOwners = _dbObjectFromDb.GetAllOwners(_sourceDb);
            var targetOwners = _dbObjectFromDb.GetAllOwners(_targetDb);

            _sql.Append("-- Set access control." + Environment.NewLine);

            foreach (var owner in sourceOwners.Values)
                if (!targetOwners.ContainsKey(owner.Id))
                    _sql.Append(_dbObjectToSql.SetOwner(owner));

            var sourcePrivs = _dbObjectFromDb.GetAllPrivileges(_sourceDb);
            var targetPrivs = _dbObjectFromDb.GetAllPrivileges(_targetDb);

            var setPrivs = new List<PgPrivilege>();

            foreach (var priv in sourcePrivs.Values)
                if (!targetPrivs.ContainsKey(priv.Id))
                    setPrivs.Add(priv);

            foreach (var priv in setPrivs.OrderBy(c => c.Target.Name).ThenByDescending(c => c.CommandType))
                _sql.Append(_dbObjectToSql.SetPrivilege(priv));

            foreach (var priv in targetPrivs.Values)
                if (!sourcePrivs.ContainsKey(priv.Id))
                {
                    var revoke = new PgPrivilege(priv.Role, priv.Target, PgPrivilegeCommandType.Revoke, priv.Types);
                    _sql.Append(_dbObjectToSql.SetPrivilege(revoke));
                }
        }

        private StringBuilder EncloseInTransaction(StringBuilder sql)
        {
            sql.Append("COMMIT");
            sql.Insert(0, String.Format("BEGIN;{0}", Environment.NewLine));
            return sql;
        }
    }
}